﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Threading;

namespace PSE.Tools.SchemaComparer.Extractor
{
    public class Package : DBObject
    {
        public string HeaderLength
        {
            get;
            set;
        }
        public string Header
        {
            get;
            set;
        }

        public string BodyLength
        {
            get;
            set;
        }
        public string Body
        {
            get;
            set;
        }

        public static DBObjectList<Package> LoadAll(OracleConnection connection)
        {
            DBObjectList<Package> ret;
            StringBuilder sqlCommand = new StringBuilder();

            //sqlCommand.Append(" SELECT DISTINCT ALL_OBJECTS.OWNER, USER_PROCEDURES.OBJECT_NAME");
            //sqlCommand.Append(" FROM  ALL_OBJECTS, USER_PROCEDURES");
            //sqlCommand.Append(" WHERE USER_PROCEDURES.OBJECT_NAME = ALL_OBJECTS.OBJECT_NAME ");
            //sqlCommand.Append(" AND USER_PROCEDURES.OBJECT_ID = ALL_OBJECTS.OBJECT_ID");
            //sqlCommand.Append(" AND USER_PROCEDURES.OBJECT_TYPE = ALL_OBJECTS.OBJECT_TYPE");
            //sqlCommand.Append(" AND USER_PROCEDURES.OBJECT_TYPE = 'PACKAGE'");
            //sqlCommand.Append(" ORDER BY USER_PROCEDURES.OBJECT_NAME");

            //recuperar o usuario utilizado para conectar para limitar o retorno da query
            string owner = (new OracleConnectionStringBuilder(connection.ConnectionString)).UserID;

            sqlCommand.Append(" SELECT DISTINCT ALL_OBJECTS.OWNER, USER_PROCEDURES.OBJECT_NAME");
            sqlCommand.Append(" FROM  ALL_OBJECTS, USER_PROCEDURES");
            sqlCommand.Append(" WHERE USER_PROCEDURES.OBJECT_NAME = ALL_OBJECTS.OBJECT_NAME ");
            sqlCommand.Append(" AND UPPER(ALL_OBJECTS.OBJECT_TYPE) = 'PACKAGE'");
            sqlCommand.Append(" AND UPPER(ALL_OBJECTS.OWNER) = UPPER(:OBJ_OWNER)");
            sqlCommand.Append(" ORDER BY USER_PROCEDURES.OBJECT_NAME");
           

            Package package;
            OracleCommand commnad = new OracleCommand(sqlCommand.ToString(), connection);
            commnad.Parameters.Add(new OracleParameter("OBJ_OWNER", owner));
            OracleDataReader reader = commnad.ExecuteReader();

            ret = new DBObjectList<Package>();
            while (reader.Read())
            {
                package = new Package();
                package.Name = reader.GetValue<string>("OBJECT_NAME");

                owner = reader.GetValue<string>("OWNER");
                //trocar o final de linha para o padrao DOS
                package.Body = UnixToDosConverter.UnixToDos(LoadPackageBody(package, connection));
                //remover o nome do owner do str
                package.Body = package.Body.Replace(string.Format("\"{0}\".", owner), "");
                //remover nova linha ou espacos do inicio e do final da string
                package.Body = package.Body.Trim('\r', '\n', ' ');
                //recuperar o numero de linhas
                package.BodyLength = package.Body.Length.ToString();


                //trocar o final de linha para o padrao DOS
                package.Header = UnixToDosConverter.UnixToDos(LoadPackageHeader(package, connection));
                //remover o nome do owner do str
                package.Header = package.Header.Replace(string.Format("\"{0}\".", owner), "");
                //remover nova linha ou espacos do inicio e do final da string
                package.Header = package.Header.Trim('\r', '\n', ' ');
                //recuperar o numero de linhas
                package.HeaderLength = package.Header.Length.ToString();

                ret.Add(package);
            }

            return ret;

        }

        private static string LoadPackageHeader(Package obj, OracleConnection connection)
        {
            return GetSource(obj.Name, Constants.PACKAGE, connection);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static string LoadPackageBody(Package obj, OracleConnection connection)
        {
            return GetSource(obj.Name, Constants.PACKAGE_BODY, connection);
        }

        //Mover para classe Source
        private static string GetSource(string objName, string objType, OracleConnection connection)
        {
            string ret = string.Empty;
            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append(" SELECT TEXT FROM USER_SOURCE WHERE NAME = :OBJ_NAME AND TYPE = :OBJ_TYPE");
            sqlCommand.Append(" ORDER BY LINE");

            OracleCommand commnad = new OracleCommand(sqlCommand.ToString(), connection);
            commnad.Parameters.Add(new OracleParameter("OBJ_NAME", objName));
            commnad.Parameters.Add(new OracleParameter("OBJ_TYPE", objType));

            OracleDataReader reader = commnad.ExecuteReader();

            StringBuilder sb = new StringBuilder();

            while (reader.Read())
            {
                sb.Append(reader.GetValue<string>("TEXT"));
            }

            return sb.ToString();
        }
    }
}
